﻿using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.Streaming;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;

namespace Common
{
    public static class ExcelHelper
    {
        #region 导出Excel

        /// <summary>
        /// 导出DataTable中指定的列到Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="title">标题</param>
        /// <param name="ExcelHeaders">指定列字典，key为DataTable字段名，value为Excel列标题</param>
        /// <param name="filepath"></param>
        /// <returns></returns>
        public static bool DataTableToExcel(DataTable dt, string title, Dictionary<string, string> ExcelHeaders, string filepath)
        {
            if (dt == null || dt.Rows.Count == 0)
                return false;

            //校验表头和DataTable列是否匹配
            List<string> ColNames = new List<string>();
            foreach (DataColumn col in dt.Columns)
                ColNames.Add(col.ColumnName);
            foreach (string key in ExcelHeaders.Keys)
            {
                if (!ColNames.Contains(key))
                    return false;
            }

            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow eRow = null;
            try
            {
                workbook = new HSSFWorkbook();
                sheet = workbook.CreateSheet("Sheet1");//创建一个名称为Sheet0的表  

                int colnum = 0;
                int rownum = 0;

                //设置标题
                CellRangeAddress cellRange = new CellRangeAddress(0, 0, 0, ExcelHeaders.Count - 1);
                sheet.AddMergedRegion(cellRange);
                eRow = sheet.CreateRow(rownum++);
                eRow.CreateCell(0).SetCellValue(title);


                //设置列头  
                eRow = sheet.CreateRow(rownum++);
                foreach (string key in ExcelHeaders.Keys)
                {
                    eRow.CreateCell(colnum++).SetCellValue(ExcelHeaders[key]);
                }
                //写入数据
                foreach (DataRow row in dt.Rows)
                {
                    colnum = 0;
                    eRow = sheet.CreateRow(rownum++);
                    foreach (string key in ExcelHeaders.Keys)
                    {
                        eRow.CreateCell(colnum++).SetCellValue(row[key].ToString());
                    }
                }

                if (File.Exists(filepath))
                    File.Delete(filepath);
                using (FileStream fs = File.OpenWrite(filepath))
                {
                    workbook.Write(fs);
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
                return false;
            }
        }



        /// <summary>
        /// 导出DataTable中指定的列到Excel，附带查询条件、制表人、制表时间
        /// 校验表头和DataTable列是否匹配
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="title">标题</param>
        /// <param name="ExcelHeaders">指定列字典，key为DataTable字段名，value为Excel列标题</param>
        /// <param name="TotlaList">需要合计的字段</param>
        /// <param name="filepath"></param>
        /// <param name="man">制表人</param>
        /// <param name="Conditions">查询条件字典：条件名称：条件值</param>
        /// <returns></returns>
        public static bool DataTableToExcelEx(DataTable dt, string title, Dictionary<string, string> ExcelHeaders, List<string> TotlaList, string filepath, string man = "", Dictionary<string, string> Conditions = null)
        {
            if (dt == null || dt.Rows.Count == 0)
                return false;

            //校验表头和DataTable列是否匹配
            List<string> ColNames = new List<string>();
            foreach (DataColumn col in dt.Columns)
                ColNames.Add(col.ColumnName.ToUpper());
            foreach (string key in ExcelHeaders.Keys)
            {
                if (!ColNames.Contains(key.ToUpper()))
                    return false;
            }

            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow eRow = null;
            ICell eCell = null;
            try
            {
                workbook = new HSSFWorkbook();
                sheet = workbook.CreateSheet("Sheet1");

                int colnum = 0;
                int rownum = 0;

                //设置标题
                ICellStyle style = CreateCellStyle(workbook, 2, 1, true);
                CellRangeAddress cellRange = new CellRangeAddress(0, 0, 0, ExcelHeaders.Count - 1);
                sheet.AddMergedRegion(cellRange);
                eRow = sheet.CreateRow(rownum++);
                colnum = 0;
                foreach (string key in ExcelHeaders.Keys)
                    eRow.CreateCell(colnum++).CellStyle = style;
                eRow.GetCell(0).SetCellValue(title);

                ICellStyle styleNor = CreateCellStyle(workbook, 1, 1, false);
                ICellStyle styleHeader = CreateCellStyle(workbook, 2, 1, true);
                #region 附加项-查询条件

                if (Conditions != null && Conditions.Count > 0)
                {
                    List<string> condt = new List<string>() { "查询条件：" };
                    foreach (string ckey in Conditions.Keys)
                        condt.Add($"\t\t{ckey} = {Conditions[ckey]}；");

                    cellRange = new CellRangeAddress(rownum, rownum, 0, ExcelHeaders.Count - 1);
                    sheet.AddMergedRegion(cellRange);
                    eRow = sheet.CreateRow(rownum++);
                    colnum = 0;
                    foreach (string key in ExcelHeaders.Keys)
                        eRow.CreateCell(colnum++).CellStyle = styleNor;
                    eRow.GetCell(0).SetCellValue(string.Join("\r\n", condt));
                }

                //创建合计字段缓存
                Dictionary<string, decimal> totalList = null;
                if (TotlaList != null && TotlaList.Count > 0)
                {
                    totalList = new Dictionary<string, decimal>();
                    foreach (string key in TotlaList)
                        totalList.Add(key, 0);
                }

                #endregion

                //设置列头 
                colnum = 0;
                eRow = sheet.CreateRow(rownum++);
                foreach (string key in ExcelHeaders.Keys)
                {
                    eCell = eRow.CreateCell(colnum++);
                    eCell.SetCellValue(ExcelHeaders[key]);
                    eCell.CellStyle = styleHeader;
                }
                //写入数据
                foreach (DataRow row in dt.Rows)
                {
                    colnum = 0;
                    eRow = sheet.CreateRow(rownum++);
                    foreach (string key in ExcelHeaders.Keys)
                    {
                        eCell = eRow.CreateCell(colnum++);
                        eCell.SetCellValue(row[key].ToString());
                        eCell.CellStyle = styleNor;

                        if (TotlaList != null && TotlaList.Count > 0)
                        {
                            string KeyUp = key.ToUpper();
                            if (TotlaList.Contains(KeyUp))
                            {
                                switch (dt.Columns["CurrentTotalGas"].DataType.Name)
                                {
                                    case "Int64":
                                        totalList[KeyUp] += Convert.ToInt64(row[key]);
                                        break;
                                    case "Int32":
                                        totalList[KeyUp] += Convert.ToInt32(row[key]);
                                        break;
                                    case "Double":
                                        totalList[KeyUp] += (Decimal)Convert.ToDouble(row[key]);
                                        break;
                                    case "Decimal":
                                        totalList[KeyUp] += Convert.ToDecimal(row[key]);
                                        break;
                                }
                            }
                        }
                    }
                }

                #region 附加项

                ICellStyle styleEx = CreateCellStyle(workbook, 3, 1, false);
                //空行
                //eRow = sheet.CreateRow(rownum++);

                //合计
                if (TotlaList != null && TotlaList.Count > 0)
                {
                    colnum = 0;
                    eRow = sheet.CreateRow(rownum++);
                    foreach (string key in ExcelHeaders.Keys)
                    {
                        eCell = eRow.CreateCell(colnum++);
                        eCell.CellStyle = styleNor;
                        if (TotlaList.Contains(key.ToUpper()))
                        {
                            eCell.SetCellValue(totalList[key.ToUpper()].ToString());
                        }
                        else
                        {
                            if (colnum == 1)
                                eCell.SetCellValue("合计");
                        }
                    }
                }

                //制表人
                if (!string.IsNullOrWhiteSpace(man))
                {
                    cellRange = new CellRangeAddress(rownum, rownum, 0, ExcelHeaders.Count - 1);
                    sheet.AddMergedRegion(cellRange);
                    eRow = sheet.CreateRow(rownum++);
                    colnum = 0;
                    foreach (string key in ExcelHeaders.Keys)
                        eRow.CreateCell(colnum++).CellStyle = styleEx;
                    eRow.GetCell(0).SetCellValue($"制表人：{man}");
                }

                //制表时间
                cellRange = new CellRangeAddress(rownum, rownum, 0, ExcelHeaders.Count - 1);
                sheet.AddMergedRegion(cellRange);
                eRow = sheet.CreateRow(rownum++);
                colnum = 0;
                foreach (string key in ExcelHeaders.Keys)
                    eRow.CreateCell(colnum++).CellStyle = styleEx;
                eRow.GetCell(0).SetCellValue($"制表时间：{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}");

                #endregion

                if (File.Exists(filepath))
                    File.Delete(filepath);
                using (FileStream fs = File.OpenWrite(filepath))
                {
                    workbook.Write(fs);
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        /// <summary>
        /// 导出DataTable中指定的列到Excel，附带查询条件、制表人、制表时间
        /// 表头比DataTable列多，有哪些列就导出哪些列
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="title"></param>
        /// <param name="ExcelHeaders"></param>
        /// <param name="TotlaList"></param>
        /// <param name="filepath"></param>
        /// <param name="man"></param>
        /// <param name="Conditions"></param>
        /// <returns></returns>
        public static bool DataTableToExcelExRealData(DataTable dt, string title, Dictionary<string, string> ExcelHeaders, List<string> TotlaList, string filepath, string man, Dictionary<string, string> Conditions = null)
        {
            if (dt == null || dt.Rows.Count == 0)
                return false;

            //校验表头和DataTable列是否匹配
            List<string> ColNames = new List<string>();
            foreach (DataColumn col in dt.Columns)
                ColNames.Add(col.ColumnName.ToUpper());

            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow eRow = null;
            ICell eCell = null;
            try
            {
                workbook = new HSSFWorkbook();
                sheet = workbook.CreateSheet("Sheet1");

                int colnum = 0;
                int rownum = 0;

                //设置标题
                ICellStyle style = CreateCellStyle(workbook, 2, 1, true);
                CellRangeAddress cellRange = new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1);
                sheet.AddMergedRegion(cellRange);
                eRow = sheet.CreateRow(rownum++);
                colnum = 0;
                foreach (DataColumn col in dt.Columns)
                    eRow.CreateCell(colnum++).CellStyle = style;
                eRow.GetCell(0).SetCellValue(title);

                ICellStyle styleNor = CreateCellStyle(workbook, 1, 1, false);
                ICellStyle styleHeader = CreateCellStyle(workbook, 2, 1, true);
                #region 附加项-查询条件

                if (Conditions != null && Conditions.Count > 0)
                {
                    List<string> condt = new List<string>() { "查询条件：" };
                    foreach (string ckey in Conditions.Keys)
                        condt.Add($"\t\t{ckey} = {Conditions[ckey]}；");

                    cellRange = new CellRangeAddress(rownum, rownum, 0, dt.Columns.Count - 1);
                    sheet.AddMergedRegion(cellRange);
                    eRow = sheet.CreateRow(rownum++);
                    colnum = 0;
                    foreach (DataColumn col in dt.Columns)
                        //foreach (string key in ExcelHeaders.Keys)
                        eRow.CreateCell(colnum++).CellStyle = styleNor;
                    eRow.GetCell(0).SetCellValue(string.Join("\r\n", condt));
                }

                //创建合计字段缓存
                Dictionary<string, decimal> totalList = null;
                if (TotlaList != null && TotlaList.Count > 0)
                {
                    totalList = new Dictionary<string, decimal>();
                    foreach (string key in TotlaList)
                        totalList.Add(key, 0);
                }

                #endregion

                //设置列头 
                colnum = 0;
                eRow = sheet.CreateRow(rownum++);
                foreach (DataColumn col in dt.Columns)
                //foreach (string key in ExcelHeaders.Keys)
                {
                    eCell = eRow.CreateCell(colnum++);
                    eCell.SetCellValue(ExcelHeaders[col.ColumnName]);
                    eCell.CellStyle = styleHeader;
                }
                //写入数据
                foreach (DataRow row in dt.Rows)
                {
                    colnum = 0;
                    eRow = sheet.CreateRow(rownum++);
                    foreach (DataColumn col in dt.Columns)
                    //foreach (string key in ExcelHeaders.Keys)
                    {
                        eCell = eRow.CreateCell(colnum++);
                        eCell.SetCellValue(row[col.ColumnName].ToString());
                        eCell.CellStyle = styleNor;

                        if (TotlaList != null && TotlaList.Count > 0)
                        {
                            string KeyUp = col.ColumnName.ToUpper();
                            if (TotlaList.Contains(KeyUp))
                            {
                                if (row.IsNull(col.ColumnName))
                                    continue;
                                switch (dt.Columns[col.ColumnName].DataType.Name)
                                {
                                    case "Int64":
                                        totalList[KeyUp] += Convert.ToInt64(row[col.ColumnName]);
                                        break;
                                    case "Int32":
                                        totalList[KeyUp] += Convert.ToInt32(row[col.ColumnName]);
                                        break;
                                    case "Double":
                                        totalList[KeyUp] += (Decimal)Convert.ToDouble(row[col.ColumnName]);
                                        break;
                                    case "Decimal":
                                        totalList[KeyUp] += Convert.ToDecimal(row[col.ColumnName]);
                                        break;
                                }
                            }
                        }
                    }
                }

                #region 附加项

                ICellStyle styleEx = CreateCellStyle(workbook, 3, 1, false);
                //空行
                //eRow = sheet.CreateRow(rownum++);

                //合计
                if (TotlaList != null && TotlaList.Count > 0)
                {
                    colnum = 0;
                    eRow = sheet.CreateRow(rownum++);
                    foreach (DataColumn col in dt.Columns)
                    //foreach (string key in ExcelHeaders.Keys)
                    {
                        eCell = eRow.CreateCell(colnum++);
                        eCell.CellStyle = styleNor;
                        if (TotlaList.Contains(col.ColumnName.ToUpper()))
                        {
                            eCell.SetCellValue(totalList[col.ColumnName.ToUpper()].ToString());
                        }
                        else
                        {
                            if (colnum == 1)
                                eCell.SetCellValue("合计");
                        }
                    }
                }

                //制表人
                cellRange = new CellRangeAddress(rownum, rownum, 0, dt.Columns.Count - 1);
                sheet.AddMergedRegion(cellRange);
                eRow = sheet.CreateRow(rownum++);
                colnum = 0;
                foreach (DataColumn col in dt.Columns)
                    //foreach (string key in ExcelHeaders.Keys)
                    eRow.CreateCell(colnum++).CellStyle = styleEx;
                eRow.GetCell(0).SetCellValue($"制表人：{man}");

                //制表时间
                cellRange = new CellRangeAddress(rownum, rownum, 0, dt.Columns.Count - 1);
                sheet.AddMergedRegion(cellRange);
                eRow = sheet.CreateRow(rownum++);
                colnum = 0;
                foreach (DataColumn col in dt.Columns)
                    //foreach (string key in ExcelHeaders.Keys)
                    eRow.CreateCell(colnum++).CellStyle = styleEx;
                eRow.GetCell(0).SetCellValue($"制表时间：{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}");

                #endregion

                if (File.Exists(filepath))
                    File.Delete(filepath);
                using (FileStream fs = File.OpenWrite(filepath))
                {
                    workbook.Write(fs);
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        //示例
        //string Title = $"用能报表";
        //filename = AppContext.BaseDirectory + $"{Title}.xls";
        //Dictionary<string, string> Headers = new Dictionary<string, string>{ {"rowid","序号" }, {"OrgName","能源节点" }, {"BuildName","能源节点" }, {"AreaName","能源节点" }, {"EnergyType","用能类型" }, };
        //Dictionary<string, string> Conditions = new Dictionary<string, string> { };
        //List<string> TotlaList = new List<string> { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31" };
        //ExcelHelper.DataTableToExcelExRealData(ds.Tables[0], Title, Headers, TotlaList, filename, model.Data.OptName, Conditions);

        #region 样式

        /// <summary>
        /// 设置单元格样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="horizontal">水平对齐：0 常规；1 左；2 中；3 右；0~7</param>
        /// <param name="vertical">垂直对齐：0 上；1 中；2 下；-1~4</param>
        /// <param name="fill">是否填充颜色</param>
        /// <param name="font">字体</param>
        /// <returns></returns>
        static ICellStyle CreateCellStyle(IWorkbook workbook, int horizontal, int vertical, bool fill, IFont font = null)
        {
            ICellStyle style = workbook.CreateCellStyle();
            //边框
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            //对齐方式
            style.Alignment = (NPOI.SS.UserModel.HorizontalAlignment)horizontal;
            style.VerticalAlignment = (VerticalAlignment)vertical;
            //是否填充颜色
            if (fill)
            {
                style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
                style.FillPattern = FillPattern.SolidForeground;
            }
            //字体
            if (font != null)
                style.SetFont(font);

            return style;
        }


        /// <summary>
        /// 设置字体
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="fontname">字体名称：黑体 宋体 楷体</param>
        /// <param name="fontheight">12</param>
        /// <param name="isbold">加粗</param>
        /// <param name="isitalic">斜体</param>
        /// <returns></returns>
        static IFont CreateFont(IWorkbook workbook, string fontname, double fontheight, bool isbold, bool isitalic)
        {
            IFont font = workbook.CreateFont();
            font.FontName = fontname;
            font.FontHeightInPoints = fontheight;
            //font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
            font.IsBold = isbold;
            font.IsItalic = isitalic;
            font.IsStrikeout = false;
            return font;
        }

        #endregion

        private static void Test()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("No", typeof(int));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Age", typeof(int));
            dt.Rows.Add(new object[] { 1, "张三", 5 });
            dt.Rows.Add(new object[] { 2, "李四", 50 });
            dt.Rows.Add(new object[] { 3, "王五", 10 });

            string fileName = AppContext.BaseDirectory + "人员Test.xls";
            string fileName2 = AppContext.BaseDirectory + "人员TestEx.xls";
            //DataTableToExcel(dt, "人员列表", new Dictionary<string, string> { { "No", "序号" }, { "Name", "姓名" }, { "Age", "年龄" } }, fileName);
            DataTableToExcelEx(dt, "人员列表", new Dictionary<string, string> { { "No", "序号" }, { "Name", "姓名" }, { "Age", "年龄" } }, null, fileName2, "测试", new Dictionary<string, string> { { "班级", "一年级" }, { "等级", "优异" } });
        }

        #endregion







        #region 读取Excel

        /// <summary>
        /// 将Excel数据读取到DateTable中
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public static DataTable ExcelToTable(string file)
        {
            IWorkbook workbook = GetWorkbook(file);
            if (workbook == null)
                return null;

            ISheet sheet = workbook.GetSheetAt(0);

            //表头  
            IRow header = sheet.GetRow(sheet.FirstRowNum);
            DataTable dt = new DataTable();
            for (int i = (int)header.FirstCellNum; i < header.LastCellNum; i++)
            {
                DataColumn column = new DataColumn(header.GetCell(i).StringCellValue);
                dt.Columns.Add(column);
            }

            //数据  
            for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
            {
                DataRow dr = dt.NewRow();
                IRow r = sheet.GetRow(i);
                for (int j = (int)r.FirstCellNum; j < r.LastCellNum; j++)
                {
                    if (dt.Columns.Count <= j)
                        continue;
                    dr[j] = r.GetCell(j);
                }
                if (string.Join("", dr.ItemArray).Trim() == "")
                    break;
                dt.Rows.Add(dr);
            }
            return dt;
        }


        /// <summary>
        /// 从Excel文件流中获取实例列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="stream">文件流</param>
        /// <param name="ext">后缀名</param>
        /// <param name="columns">列名字典</param>
        /// <param name="errMsg">错误提示</param>
        /// <returns></returns>
        public static List<T> ExcelToModel<T>(this Stream stream, string ext, Dictionary<string, string> columndic, out string errMsg) where T : new()
        {
            errMsg = "成功";
            try
            {
                IWorkbook workbook = GetWorkbook(stream, ext);
                ISheet sheet = workbook.GetSheetAt(0);

                #region 校验表头
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<string> columns = new List<string>();
                for (int i = 0; i < header.LastCellNum; i++)
                    columns.Add(header.GetCell(i).StringCellValue);

                Dictionary<string, int> keyIndex = new Dictionary<string, int>();
                foreach (string key in columndic.Keys)
                {
                    if (!columns.Contains(key))
                    {
                        errMsg = "模板错误！";
                        return null;
                    }
                    keyIndex.Add(columndic[key], columns.IndexOf(key));    //记录各个列的位置
                }
                #endregion

                #region 读取数据
                List<T> result = new List<T>();
                Type type = typeof(T);
                PropertyInfo[] propertyInfos = type.GetProperties();
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    //ExpandoObject obj = new ExpandoObject();

                    IRow r = sheet.GetRow(i);
                    T obj = new T();
                    foreach (PropertyInfo propertyInfo in propertyInfos)
                    {
                        if (!columndic.Values.Contains(propertyInfo.Name))
                            continue;
                        string val = r.Cells[keyIndex[propertyInfo.Name]].StringCellValue;
                        switch (propertyInfo.PropertyType.FullName)
                        {
                            case "System.Int64":
                                if (Int64.TryParse(val, out long val2))
                                    propertyInfo.SetValue(obj, val2);
                                break;
                            case "System.Int32":
                                if (Int32.TryParse(val, out int val3))
                                    propertyInfo.SetValue(obj, val3);
                                break;
                            case "System.String":
                                propertyInfo.SetValue(obj, val);
                                break;
                            case "System.Double":
                                if (Double.TryParse(val, out double val4))
                                    propertyInfo.SetValue(obj, val4);
                                break;
                            case "System.Decimal":
                                if (Decimal.TryParse(val, out decimal val5))
                                    propertyInfo.SetValue(obj, val5);
                                break;
                            case "System.DateTime":
                                if(DateTime.TryParse(val, out DateTime val6))
                                propertyInfo.SetValue(obj, val6);
                                break;
                            case "System.Boolean":
                                propertyInfo.SetValue(obj, val=="1");
                                break;
                        }
                    }
                    result.Add(obj);
                }
                return result;
                #endregion
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                return null;
            }
        }
        //string ext = ".xls"; string errorMsg;
        //Dictionary<string, string> columns = new Dictionary<string, string> {
        //            { "点位名称","PointName"},
        //            { "点位类别ID","PointType"},
        //            { "所属校区ID","OwnerSchoolId"},
        //            { "所属校区名称","OwnerSchoolName"},
        //            { "所属区域ID","OwnerAreaId"},
        //            { "所属区域名称","OwnerAreaName"},
        //            { "所属楼栋ID","OwnerBuildId"},
        //            { "所属楼栋名称","OwnerBuildName"},
        //            { "所属部门ID","OwnerPortId"},
        //            { "所属部门名称","OwnerPortName"},
        //            { "区域负责人","AreaMgr"},
        //            { "联系电话","Phone"},
        //            { "点位描述","Description"}
        //        };
        //List<PointPositionModel> points = stream.ExcelToModel<PointPositionModel>(ext, columns, out errorMsg);



        #region 工具方法

        static IWorkbook GetWorkbook(string filename)
        {
            IWorkbook workbook;
            string fileExt = Path.GetExtension(filename).ToLower();

            using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (fileExt == ".xlsx")
                    workbook = new XSSFWorkbook(fs);
                else if (fileExt == ".xls")
                    workbook = new HSSFWorkbook(fs);
                else
                    workbook = null;
            }

            return workbook;
        }

        static IWorkbook GetWorkbook(Stream stream, string ext)
        {
            IWorkbook workbook;

            if (ext == ".xlsx")
                workbook = new XSSFWorkbook(stream);
            else if (ext == ".xls")
                workbook = new HSSFWorkbook(stream);
            else
                workbook = null;

            return workbook;
        }

        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell">目标单元格</param>
        /// <returns></returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return null;
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                case CellType.Numeric:
                    return cell.NumericCellValue;
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Error:
                    return cell.ErrorCellValue;
                case CellType.Formula:
                default:
                    return "=" + cell.CellFormula;
            }
        }

        #endregion

        #endregion
    }

    static class ExcleExtension
    {

        public static IWorkbook GetWorkbook(this string filename)
        {
            IWorkbook workbook;
            string fileExt = Path.GetExtension(filename).ToLower();

            using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (fileExt == ".xlsx")
                    workbook = new XSSFWorkbook(fs);
                else if (fileExt == ".xls")
                    workbook = new HSSFWorkbook(fs);
                else
                    workbook = null;
            }
            return workbook;
        }

        public static IWorkbook GetWorkbook(this Stream stream, string ext)
        {
            IWorkbook workbook;

            if (ext == ".xlsx")
                workbook = new XSSFWorkbook(stream);
            else if (ext == ".xls")
                workbook = new HSSFWorkbook(stream);
            else
                workbook = null;

            return workbook;
        }

        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell">目标单元格</param>
        /// <returns></returns>
        public static object GetValueType(this ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return null;
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                case CellType.Numeric:
                    return cell.NumericCellValue;
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Error:
                    return cell.ErrorCellValue;
                case CellType.Formula:
                default:
                    return "=" + cell.CellFormula;
            }
        }

        /// <summary>
        /// 添加行且添加单元格
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="rowIdx">第几行</param>
        /// <param name="count">单元格数量</param>
        /// <param name="style"></param>
        public static void CreateRow(this ISheet sheet, int rowIdx, int count, ICellStyle style = null)
        {
            IRow eRow = sheet.CreateRow(rowIdx);
            for (int i = 0; i < count; i++)
            {
                ICell cell = eRow.CreateCell(i);
                cell.CellStyle = style;
            }
        }
        //sheet.CreateRow(rownum, colcnt, style);

        public static void CreateCells(this IRow eRow, int count, ICellStyle style = null)
        {
            for (int i = 0; i < count; i++)
            {
                ICell cell = eRow.CreateCell(i);
                cell.CellStyle = style;
            }
        }

        public static void SetCellValue(this ISheet sheet, int r, int c, string data)
        {
            sheet.GetRow(r).GetCell(c).SetCellValue(data);
        }

        /// <summary>
        /// 设置单元格样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="horizontal">水平对齐：0 常规；1 左；2 中；3 右；0~7</param>
        /// <param name="vertical">垂直对齐：0 上；1 中；2 下；-1~4</param>
        /// <param name="fill">是否填充颜色</param>
        /// <param name="font">字体</param>
        /// <returns></returns>
        public static ICellStyle CreateCellStyle(IWorkbook workbook, int horizontal, int vertical, bool fill, IFont font = null)
        {
            ICellStyle style = workbook.CreateCellStyle();
            //边框
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            //对齐方式
            style.Alignment = (NPOI.SS.UserModel.HorizontalAlignment)horizontal;
            style.VerticalAlignment = (VerticalAlignment)vertical;
            //是否填充颜色
            if (fill)
            {
                style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
                style.FillPattern = FillPattern.SolidForeground;
            }
            //字体
            if (font != null)
                style.SetFont(font);

            return style;
        }

        /// <summary>
        /// 设置字体
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="fontname">字体名称：黑体 宋体 楷体</param>
        /// <param name="fontheight">12</param>
        /// <param name="isbold">加粗</param>
        /// <param name="isitalic">斜体</param>
        /// <returns></returns>
        public static IFont CreateFont(IWorkbook workbook, string fontname, double fontheight, bool isbold, bool isitalic)
        {
            IFont font = workbook.CreateFont();
            font.FontName = fontname;
            font.FontHeightInPoints = fontheight;
            //font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
            font.IsBold = isbold;
            font.IsItalic = isitalic;
            font.IsStrikeout = false;
            return font;
        }
    }
}
